﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;


namespace CCPSIP
{
    public class OracleKitReg
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };

        private const int kIdHeaderLength = 8;
        private const int kCheckcodeLength = 64; //SHA-256
        private const string kTimePasswordFormat = ",yyyyMMdd_HHmm";
        private const int kTimeDeviationRange = 10; //in minutes

        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }

        /// <summary>
        /// CCPSIP寫入註冊資料，Insert到reg_apply Table
        /// </summary>
        /// <param name="paramDict">參數Dictionary</param>
        /// <returns>新增的ApplyID</returns>
        public static UInt32 InsertRegApply(Dictionary<string, string> paramDict)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                DateTime reqTime = DateTime.Now;

                connOracle.Open();

                //【01】取消User舊的註冊資料
                //
                CancelRegister("C", paramDict["UserCountry"], paramDict["UserPhone"], paramDict["DeviceType"]);


                //【02】寫入reg_apply
                //
                string ErrorMsg = paramDict["ErrorMsg"];
                string insStr = "INSERT INTO reg_apply"
                  + " (DeviceType, UserCountry, UserPhone, FullName, UserID, UserType, CreateTime, ApplyStatus,"
                  + "  FriendCountry, FriendPhone, FriendName, Remark, LangID, RegionID, StatusTime, Pushed,"
                  + "  IPAddr, OSVer, ErrorMsg, UserAgent)"
                  + " VALUES"
                  + " (:DeviceType, :UserCountry, :UserPhone, :FullName, :UserID, :UserType, :CreateTime, :ApplyStatus,"
                  + "  :FriendCountry, :FriendPhone, :FriendName, :Remark, :LangID, :RegionID, :DateTime, :Pushed,"
                  + "  :IPAddr, :OSVer, :ErrorMsg, :UserAgent)"
                  + " RETURNING pid INTO :pid";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle);
                insCmd.Parameters.Add("DeviceType", OracleType.VarChar).Value = paramDict["DeviceType"];
                insCmd.Parameters.Add("UserCountry", OracleType.VarChar).Value = paramDict["UserCountry"];
                insCmd.Parameters.Add("UserPhone", OracleType.VarChar).Value = paramDict["UserPhone"];
                insCmd.Parameters.Add("FullName", OracleType.VarChar).Value = (string.IsNullOrEmpty(paramDict["FullName"]) ? "" : paramDict["FullName"]);
                insCmd.Parameters.Add("UserID", OracleType.VarChar).Value = (string.IsNullOrEmpty(paramDict["UserID"]) ? "" : paramDict["UserID"]);
                insCmd.Parameters.Add("UserType", OracleType.VarChar).Value = paramDict["UserType"];
                insCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime;
                insCmd.Parameters.Add("ApplyStatus", OracleType.VarChar).Value = (string.IsNullOrEmpty(ErrorMsg) ? "0" : "E");
                insCmd.Parameters.Add("FriendCountry", OracleType.VarChar).Value = paramDict["FriendCountry"];
                insCmd.Parameters.Add("FriendPhone", OracleType.VarChar).Value = paramDict["FriendPhone"];
                insCmd.Parameters.Add("FriendName", OracleType.VarChar).Value = paramDict["FriendName"];
                insCmd.Parameters.Add("Remark", OracleType.VarChar).Value = (string.IsNullOrEmpty(paramDict["Remark"]) ? "" : paramDict["Remark"]);
                insCmd.Parameters.Add("LangID", OracleType.VarChar).Value = (string.IsNullOrEmpty(paramDict["LangID"]) ? "" : paramDict["LangID"]);
                insCmd.Parameters.Add("RegionID", OracleType.VarChar).Value = (string.IsNullOrEmpty(paramDict["RegionID"]) ? "" : paramDict["RegionID"]);
                insCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                insCmd.Parameters.Add("Pushed", OracleType.VarChar).Value = "N";
                insCmd.Parameters.Add("IPAddr", OracleType.VarChar).Value = (string.IsNullOrEmpty(paramDict["IPAddr"]) ? "" : paramDict["IPAddr"]);
                insCmd.Parameters.Add("OSVer", OracleType.VarChar).Value = (string.IsNullOrEmpty(paramDict["OSVer"]) ? "" : paramDict["OSVer"]);
                insCmd.Parameters.Add("ErrorMsg", OracleType.VarChar).Value = (string.IsNullOrEmpty(ErrorMsg) ? "" : ErrorMsg);
                insCmd.Parameters.Add("UserAgent", OracleType.VarChar).Value = (string.IsNullOrEmpty(paramDict["UserAgent"]) ? "" : paramDict["UserAgent"]);

                insCmd.Parameters.Add(new OracleParameter
                {
                    ParameterName = ":pid",
                    OracleType = OracleType.Number,
                    Direction = ParameterDirection.Output
                });

                insCmd.ExecuteNonQuery();

                return Convert.ToUInt32(insCmd.Parameters[":pid"].Value);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 統計註冊錯誤次數(阻擋DoS,暴力測試)
        /// </summary>
        /// <param name="IPAddr">Client IP</param>
        /// <param name="allowTime">統計起始時間</param>
        /// <returns>錯誤次數</returns>
        public static UInt32 CountApplyError(string IPAddr, DateTime allowTime)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT COUNT(*) FROM reg_apply"
                  + " WHERE ApplyStatus='E' AND IPAddr=:IPAddr AND CreateTime>:CreateTime";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("IPAddr", OracleType.VarChar).Value = IPAddr;
                selCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = allowTime;
                return Convert.ToUInt32(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 新增門號驗證碼，Insert到reg_auth Table
        /// </summary>
        /// <param name="ApplyID">註冊資料的Apply ID</param>
        /// <param name="AuthCode">門號驗證碼</param>
        /// <param name="ExpiryTime">驗證碼過期時間</param>
        public static void InsertRegAuth(UInt32 ApplyID, string AuthCode, DateTime ExpiryTime)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string insStr = "INSERT INTO reg_auth"
                  + " (ApplyID, CreateTime, AuthCode, ExpiryTime, AUTHFINISH, ERRORCOUNT)"
                  + " VALUES"
                  + " (:ApplyID, :DateTime, :AuthCode, :ExpiryTime, :AUTHFINISH, :ERRORCOUNT)";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle);
                insCmd.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;
                insCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                insCmd.Parameters.Add("AuthCode", OracleType.VarChar).Value = AuthCode;
                insCmd.Parameters.Add("ExpiryTime", OracleType.DateTime).Value = ExpiryTime;
                insCmd.Parameters.Add("AUTHFINISH", OracleType.VarChar).Value = "N";
                insCmd.Parameters.Add("ERRORCOUNT", OracleType.VarChar).Value = "0";
                insCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 記錄門號認證簡訊的HiNet發送結果
        /// </summary>
        /// <param name="ApplyID">註冊資料的Apply ID</param>
        /// <param name="HiNet_ID">HiNet MessageID</param>
        public static void UpdateRegAuthHiNetID(UInt32 ApplyID, string HiNet_ID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string updStr1 = "UPDATE reg_auth SET HiNet_ID=:HiNet_ID"
                  + " WHERE ApplyID=:ApplyID";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                updCmd1.Parameters.Add("HiNet_ID", OracleType.VarChar).Value = HiNet_ID;
                updCmd1.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;
                updCmd1.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 統計容許時間內的認證次數(阻擋DoS)
        /// </summary>
        /// <param name="IPAddr">Client IP</param>
        /// <param name="allowTime">容許時間</param>
        /// <returns>容許時間內的認證次數</returns>
        public static UInt32 CountRegAuth(string IPAddr, DateTime allowTime)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT COUNT(*) FROM reg_auth AU, reg_apply AP"
                  + " WHERE AU.ApplyID=AP.pid"
                  + " AND AP.IPAddr=:IPAddr AND AU.CreateTime>:CreateTime";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("IPAddr", OracleType.VarChar).Value = IPAddr;
                selCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = allowTime;
                return Convert.ToUInt32(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// CCPSIP 手機門號驗證，回傳ApplyID
        /// </summary>
        /// <param name="UserCountry">手機門號國碼</param>
        /// <param name="UserPhone">手機門號</param>
        /// <param name="AuthCode">驗證碼</param>
        /// <returns>驗證成功回傳ApplyID，失敗回傳-1。</returns>
        public static int ValidateRegAuth(string UserCountry, string UserPhone, string AuthCode)
        {
            if (AuthCode.Length < 6)
                return -1;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT AU.ApplyID, AU.AuthCode FROM reg_auth AU, reg_apply AP"
                  + " WHERE AU.ApplyID=AP.pid AND AU.AuthFinish='N'"
                  + " AND AU.ErrorCount<10 AND AU.ExpiryTime>=:DateTime"
                  + " AND AP.UserCountry=:UserCountry AND AP.UserPhone=:UserPhone"
                  + " ORDER BY AU.CreateTime DESC, AU.ApplyID DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                selCmd.Parameters.Add("UserCountry", OracleType.VarChar).Value = UserCountry;
                selCmd.Parameters.Add("UserPhone", OracleType.VarChar).Value = UserPhone;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow row = dataSet.Tables[0].Rows[0];
                    int ApplyID = Convert.ToInt32(row["ApplyID"]);
                    string legalCode = Convert.ToString(row["AuthCode"]);

                    if (AuthCode == legalCode)
                    {
                        //【01-1】記錄驗證碼已使用
                        //
                        string updStr1 = "UPDATE reg_auth SET AuthFinish='Y' WHERE ApplyID=:ApplyID";
                        OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle);
                        updCmd1.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;
                        updCmd1.ExecuteNonQuery();

                        //【01-2】記錄門號已驗證
                        //
                        string updStr2 = "UPDATE reg_apply SET ApplyStatus='1', StatusTime=:DateTime"
                          + " WHERE pid=:ApplyID AND ApplyStatus='0'";
                        OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle);
                        updCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                        updCmd2.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;
                        updCmd2.ExecuteNonQuery();

                        return ApplyID;
                    }
                    else
                    {
                        //【02】累加驗證錯誤次數
                        //
                        string updStr = "UPDATE reg_auth SET ErrorCount=ErrorCount+1 WHERE ApplyID=:ApplyID";
                        OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                        updCmd.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;
                        updCmd.ExecuteNonQuery();
                    }
                }
            }
            finally
            {
                connOracle.Close();
            }
            return -1;
        }


        /// <summary>
        /// CCPSIP記錄Device UUID到待確認區
        /// </summary>
        /// <param name="ApplyID">註冊資料的Apply ID</param>
        /// <param name="UUID">Device UUID</param>
        /// <param name="DeviceToken">Device Push Token</param>
        /// <param name="PushType">推播類型</param>
        public static void InsertRegAuthDevice(int ApplyID, string UUID, string DeviceToken, string PushType)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                DateTime reqTime = DateTime.Now;

                connOracle.Open();
                myTrans = connOracle.BeginTransaction();

                string insStr = "INSERT INTO reg_auth_device"
                  + " (ApplyID, CreateTime, DeviceUUID, TokenTime, PushType, DeviceToken) VALUES"
                  + " (:ApplyID, :CreateTime, :DeviceUUID, :TokenTime, :PushType, :DeviceToken)";
                OracleCommand insCmd = new OracleCommand(insStr, connOracle, myTrans);
                insCmd.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;
                insCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime;
                insCmd.Parameters.Add("DeviceUUID", OracleType.VarChar).Value = UUID;
                insCmd.Parameters.Add("TokenTime", OracleType.DateTime).Value = reqTime;
                insCmd.Parameters.Add("PushType", OracleType.VarChar).Value = PushType;
                insCmd.Parameters.Add("DeviceToken", OracleType.VarChar).Value = DeviceToken;
                insCmd.ExecuteNonQuery();

                string updStr = "UPDATE reg_apply SET DeviceUUID=:DeviceUUID WHERE pid=:ApplyID";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle, myTrans);
                updCmd.Parameters.Add("DeviceUUID", OracleType.VarChar).Value = UUID;
                updCmd.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;
                updCmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 更新待確認區的Device Push Token
        /// </summary>
        /// <param name="DeviceUUID">Device UUID</param>
        /// <param name="DeviceToken">Device Push Token</param>
        /// <param name="PushType">推播類型</param>
        public static void UpdateRegAuthDevice(string DeviceUUID, string DeviceToken, string PushType)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string updStr = "UPDATE reg_auth_device"
                  + " SET TokenTime=:DateTime, PushType=:PushType, DeviceToken=:DeviceToken"
                  + " WHERE DeviceUUID=:DeviceUUID";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                updCmd.Parameters.Add("PushType", OracleType.VarChar).Value = PushType;
                updCmd.Parameters.Add("DeviceToken", OracleType.VarChar).Value = DeviceToken;
                updCmd.Parameters.Add("DeviceUUID", OracleType.VarChar).Value = DeviceUUID;
                updCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得註冊中的裝置 UUID 字串
        /// </summary>
        /// <param name="idHeader">UUID 的開頭字串</param>
        /// <param name="checkcode">UUID 的 Time Based 驗證碼</param>
        /// <returns>若通過驗證則回傳裝置 UUID 字串，否則null</returns>
        public static string getAuthUUID(string idHeader, string checkcode)
        {
            DateTime reqTime = DateTime.Now;

            //【01】檢查參數
            //
            try
            {
                if (idHeader.Length < kIdHeaderLength)
                    return null;
                if (checkcode.Length < kCheckcodeLength)
                    return null;

                checkcode = checkcode.ToLower();
            }
            catch (Exception ex)
            {
                ChrisKit.SkipVar(ex);
                return null;
            }

            //【02】產生可容忍時間的字串List
            //
            string[] timeStrArray = new string[kTimeDeviationRange * 2 + 1];
            timeStrArray[0] = reqTime.ToString(kTimePasswordFormat);
            for (int i = 1; i <= kTimeDeviationRange; i++)
            {
                timeStrArray[i * 2 - 1] = reqTime.AddMinutes(i).ToString(kTimePasswordFormat);
                timeStrArray[i * 2] = reqTime.AddMinutes(-i).ToString(kTimePasswordFormat);
            }

            //【03-1】連結DB，篩選符合的Device
            //
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr = "SELECT DeviceUUID FROM reg_apply"
                  + " WHERE ApplyStatus IN ('1','Y','N')"
                  + " AND CreateTime>=:CreateTime AND DeviceUUID LIKE :idHeader"
                  + " ORDER BY CreateTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime.AddDays(-90);
                selCmd.Parameters.Add("idHeader", OracleType.VarChar).Value = idHeader + "%";
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                //【03-2】比對Hash字串
                //
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    string uuid = Convert.ToString(row["DeviceUUID"]);
                    foreach (string timeStr in timeStrArray)
                    {
                        if (checkcode == HashKit.getHashStr(uuid + timeStr, HashType.SHA256))
                            return uuid;
                    }
                }
                return null;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得裝置的註冊狀態
        /// </summary>
        /// <param name="DeviceUUID">Device UUID</param>
        /// <returns>註冊狀態代碼</returns>
        public static string getApplyStatus(string DeviceUUID)
        {
            DateTime reqTime = DateTime.Now;
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT ApplyStatus FROM reg_apply"
                  + " WHERE CreateTime>=:CreateTime AND DeviceUUID=:DeviceUUID"
                  + " ORDER BY CreateTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime.AddDays(-90);
                selCmd.Parameters.Add("DeviceUUID", OracleType.VarChar).Value = DeviceUUID;
                return Convert.ToString(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得註冊時填寫的朋友姓名
        /// </summary>
        /// <param name="DeviceUUID">Device UUID</param>
        /// <returns>CCPSIP朋友姓名</returns>
        public static string getFriendName(string DeviceUUID)
        {
            DateTime reqTime = DateTime.Now;
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT FriendName FROM reg_apply"
                  + " WHERE CreateTime>=:CreateTime AND DeviceUUID=:DeviceUUID"
                  + " ORDER BY CreateTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime.AddDays(-90);
                selCmd.Parameters.Add("DeviceUUID", OracleType.VarChar).Value = DeviceUUID;
                return Convert.ToString(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得等待User確認的RegApplyInfo Array
        /// </summary>
        /// <param name="userInfo">SipUserInfo物件</param>
        /// <returns>待確認的RegApplyInfo Array</returns>
        public static RegApplyInfo[] getRegConfirmList(SipUserInfo userInfo)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT * FROM reg_apply"
                  + " WHERE ApplyStatus='1'"
                  + " AND FriendCountry=:FriendCountry AND FriendPhone=:FriendPhone"
                  + " ORDER BY StatusTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("FriendCountry", OracleType.VarChar).Value = userInfo.CountryCode;
                selCmd.Parameters.Add("FriendPhone", OracleType.VarChar).Value = userInfo.MobileNum;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                List<RegApplyInfo> resultList = new List<RegApplyInfo>();
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    resultList.Add(new RegApplyInfo(row));
                }
                return resultList.ToArray();
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取消User註冊資料(退回或取消)
        /// </summary>
        /// <param name="FinalStatus">Final狀態碼</param>
        /// <param name="UserCountry">User手機門號國碼</param>
        /// <param name="UserPhone">User手機門號</param>
        /// <param name="DeviceType">裝置類型</param>
        public static void CancelRegister(string FinalStatus, string UserCountry, string UserPhone, string DeviceType)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                //【01】檢查User是否有註冊到一半的資料
                //
                string selStr = "SELECT pid FROM reg_apply"
                  + " WHERE CreateTime>=:CreateTime AND ApplyStatus IN ('0','1')"
                  + " AND UserCountry=:UserCountry AND UserPhone=:UserPhone AND DeviceType=:DeviceType";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("CreateTime", OracleType.DateTime).Value = DateTime.Now.AddYears(-3);
                selCmd.Parameters.Add("UserCountry", OracleType.VarChar).Value = UserCountry;
                selCmd.Parameters.Add("UserPhone", OracleType.VarChar).Value = UserPhone;
                selCmd.Parameters.Add("DeviceType", OracleType.VarChar).Value = DeviceType;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    List<string> idList = new List<string>();
                    foreach (DataRow row in dataSet.Tables[0].Rows)
                    {
                        idList.Add(Convert.ToString(row["pid"]));
                    }
                    string idJoinStr = string.Join(",", idList.ToArray());


                    //【02-1】記錄User回第一步重新註冊
                    //
                    string updStr = "UPDATE reg_apply"
                      + " SET ApplyStatus=:ApplyStatus, StatusTime=:DateTime, Pushed='N'"
                      + string.Format(" WHERE pid IN ({0})", idJoinStr);
                    OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                    updCmd.Parameters.Add("ApplyStatus", OracleType.VarChar).Value = FinalStatus;
                    updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    updCmd.ExecuteNonQuery();


                    if (FinalStatus == "C")
                    {
                        //【02-2】清除待確認區的Device Token
                        //
                        string delStr = "DELETE FROM reg_auth_device"
                          + string.Format(" WHERE ApplyID IN ({0})", idJoinStr);
                        OracleCommand delCmd = new OracleCommand(delStr, connOracle);
                        delCmd.ExecuteNonQuery();
                    }
                }
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得待確認的User註冊資料
        /// </summary>
        /// <param name="UserPhone">User手機門號(含國碼)</param>
        /// <param name="DeviceType">裝置類型</param>
        /// <returns>註冊資料的 RegApplyInfo 物件</returns>
        public static RegApplyInfo getRegApplyInfo(string UserPhone, string DeviceType)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT A.*, D.PushType, D.DeviceToken"
                  + " FROM reg_apply A"
                  + " LEFT JOIN reg_auth_device D ON A.pid=D.ApplyID"
                  + " WHERE ApplyStatus='1'"
                  + "   AND CONCAT(UserCountry, UserPhone)=:UserPhone AND DeviceType=:DeviceType"
                  + " ORDER BY StatusTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("UserPhone", OracleType.VarChar).Value = UserPhone;
                selCmd.Parameters.Add("DeviceType", OracleType.VarChar).Value = DeviceType;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    return new RegApplyInfo(dataSet.Tables[0].Rows[0]);
                }
                return null;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得「非CCP總公司」會員的SIP號碼
        /// </summary>
        /// <param name="CountryCode">手機門號國碼</param>
        /// <param name="MobileNumber">手機門號</param>
        /// <returns>若申請過則回傳原號碼，否則產生一組新號碼回傳</returns>
        public static string GetGuestSIPNumber(string CountryCode, string MobileNumber)
        {
            string SIP_Num = null;
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                //【01】查詢是否有申請過的號碼
                string selStr = "SELECT SIP_Num FROM guest_number"
                  + " WHERE CountryCode=:CountryCode AND MobileNum=:MobileNum";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("CountryCode", OracleType.VarChar).Value = CountryCode;
                selCmd.Parameters.Add("MobileNum", OracleType.VarChar).Value = MobileNumber;
                SIP_Num = Convert.ToString(selCmd.ExecuteScalar());

                if (string.IsNullOrEmpty(SIP_Num))
                {
                    //【02】首次申請則產生一組新號碼
                    string insStr = "INSERT INTO guest_number"
                      + " (CountryCode, MobileNum, CreateTime) VALUES"
                      + " (:CountryCode, :MobileNum, :DateTime)"
                      + " RETURNING SIP_NUM INTO :SIP_NUM";
                    OracleCommand insCmd = new OracleCommand(insStr, connOracle);
                    insCmd.Parameters.Add("CountryCode", OracleType.VarChar).Value = CountryCode;
                    insCmd.Parameters.Add("MobileNum", OracleType.VarChar).Value = MobileNumber;
                    insCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                    insCmd.Parameters.Add(new OracleParameter
                    {
                        ParameterName = ":SIP_NUM",
                        OracleType = OracleType.Number,
                        Direction = ParameterDirection.Output
                    });
                    insCmd.ExecuteNonQuery();
                    SIP_Num = Convert.ToString(insCmd.Parameters[":SIP_NUM"].Value);
                }
                return SIP_Num;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 開通CCPSIP會員權限
        /// </summary>
        /// <param name="regInfo">註冊資料的 RegApplyInfo 物件</param>
        /// <param name="SIP_Num">會員SIP號碼</param>
        public static void ActivateRegUser(RegApplyInfo regInfo, string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();
                DateTime reqTime = DateTime.Now;

                bool is_CCP_Employee = false;

                //【01】清除會員舊帳號Push Token
                //
                string oldUUID1 = OracleKitUser.getUUIDbyPushToken(regInfo.DeviceToken, regInfo.PushType, connOracle);
                string oldUUID2 = OracleKitUser.getUUID(SIP_Num, connOracle);
                if (string.IsNullOrEmpty(oldUUID1))
                    oldUUID1 = "EMPTY_UUID";
                if (string.IsNullOrEmpty(oldUUID2))
                    oldUUID2 = "EMPTY_UUID";

                //【03】非總公司，一位員工只能裝一台iSender
                //
                if (SipKit.isCCPUser(regInfo.UserType) && regInfo.UserType != "CCP_HQ")
                {
                    string PPS_CODE = regInfo.UserID;

                    if (!OracleKitUser.canHaveMultiPhones(PPS_CODE, connOracle))
                    {
                        string oldUUID3 = OracleKitUser.getUUIDbyEmpID(PPS_CODE, connOracle);

                        if (!string.IsNullOrEmpty(oldUUID3))
                        {
                            string delStr3 = "DELETE FROM device_token WHERE UUID=:UUID";
                            OracleCommand delCmd3 = new OracleCommand(delStr3, connOracle, myTrans);
                            delCmd3.Parameters.Add("UUID", OracleType.VarChar).Value = oldUUID3;
                            delCmd3.ExecuteNonQuery();
                        }

                        is_CCP_Employee = true;
                    }
                }

                myTrans = connOracle.BeginTransaction();

                string delStr1 = "DELETE FROM device_token WHERE UUID IN (:UUID0, :UUID1, :UUID2)";
                OracleCommand delCmd1 = new OracleCommand(delStr1, connOracle, myTrans);
                delCmd1.Parameters.Add("UUID0", OracleType.VarChar).Value = regInfo.DeviceUUID;
                delCmd1.Parameters.Add("UUID1", OracleType.VarChar).Value = oldUUID1;
                delCmd1.Parameters.Add("UUID2", OracleType.VarChar).Value = oldUUID2;
                delCmd1.ExecuteNonQuery();


                //【02】停用會員舊帳號
                //
                string updStr2 = "UPDATE reg_user SET Status='N', DisableTime=:DateTime"
                  + " WHERE Status='Y' AND (SIP_Num=:SIP_Num OR UUID IN (:UUID0, :UUID1))";
                OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle, myTrans);
                updCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                updCmd2.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                updCmd2.Parameters.Add("UUID0", OracleType.VarChar).Value = regInfo.DeviceUUID;
                updCmd2.Parameters.Add("UUID1", OracleType.VarChar).Value = oldUUID1;
                updCmd2.ExecuteNonQuery();

                if (is_CCP_Employee)
                {
                    string updStr3 = "UPDATE reg_user SET Status='N', DisableTime=:DateTime"
                          + " WHERE Status='Y' AND PPS_CODE=:PPS_CODE";
                    OracleCommand updCmd3 = new OracleCommand(updStr3, connOracle, myTrans);
                    updCmd3.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                    updCmd3.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = regInfo.UserID;
                    updCmd3.ExecuteNonQuery();
                }

                //【04】清除所有開通前的聊天記錄
                //
                CleanAllOldChats(SIP_Num);


                //【05】亂數產生密碼原則
                //
                HashType hashType = (HashType)RNG.Next(3);
                string hashCode = HashKit.getHashCode(hashType);
                int hashStart = RNG.Next(2, 8);
                int hashLen = RNG.Next(16, 20);


                //【06】啟用會員新帳號
                //
                string insStr6 = "INSERT INTO reg_user"
                  + " (CountryCode, MobileNum, UUID, DeviceType, CreateTime,"
                  + "  SIP_Num, Status, PPS_CODE, FullName, CustomName, UserType, ABType,"
                  + "  HashType, HashStart, HashLen, ApplyID) VALUES"
                  + " (:CountryCode, :MobileNum, :UUID, :DeviceType, :DateTime,"
                  + "  :SIP_Num, :Status , :PPS_CODE, :FullName, :CustomName, :UserType, :UserType,"
                  + "  :HashType, :HashStart, :HashLen, :ApplyID)";
                OracleCommand insCmd6 = new OracleCommand(insStr6, connOracle, myTrans);
                insCmd6.Parameters.Add("CountryCode", OracleType.VarChar).Value = regInfo.UserCountry;
                insCmd6.Parameters.Add("MobileNum", OracleType.VarChar).Value = regInfo.UserPhone;
                insCmd6.Parameters.Add("UUID", OracleType.VarChar).Value = regInfo.DeviceUUID;
                if (regInfo.OSVer.StartsWith("Android "))
                    insCmd6.Parameters.Add("DeviceType", OracleType.VarChar).Value = "Android";
                else
                    insCmd6.Parameters.Add("DeviceType", OracleType.VarChar).Value = regInfo.DeviceType;
                insCmd6.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                insCmd6.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                insCmd6.Parameters.Add("Status", OracleType.VarChar).Value = "Y";
                insCmd6.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = (string.IsNullOrEmpty(regInfo.UserID) ? "" : regInfo.UserID);
                insCmd6.Parameters.Add("FullName", OracleType.VarChar).Value = regInfo.FullName;
                insCmd6.Parameters.Add("CustomName", OracleType.VarChar).Value = ChrisKit.Substr(regInfo.FullName, 0, 20);
                insCmd6.Parameters.Add("UserType", OracleType.VarChar).Value = regInfo.UserType;
                insCmd6.Parameters.Add("HashType", OracleType.VarChar).Value = hashCode;
                insCmd6.Parameters.Add("HashStart", OracleType.Int32).Value = hashStart;
                insCmd6.Parameters.Add("HashLen", OracleType.Int32).Value = hashLen;
                insCmd6.Parameters.Add("ApplyID", OracleType.Int32).Value = regInfo.ApplyID;
                insCmd6.ExecuteNonQuery();


                //【07】記錄Device Push Token
                //2014/12/23更新：
                //因以前MySQL可以直接插入null值, Oracle不行, 所以這邊要加入判斷,
                //否則朋友在開通時會跳出ORA錯誤
                string insStr7 = null;

                if (string.IsNullOrEmpty(regInfo.DeviceToken))
                {
                    insStr7 = "INSERT INTO device_token"
                            + " (CreateTime, UpdateTime, Status, UUID, PushType) VALUES"
                            + " (:CreateTime, :UpdateTime, :Status, :UUID, :PushType)";
                    OracleCommand insCmd7 = new OracleCommand(insStr7, connOracle, myTrans);
                    insCmd7.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime;
                    insCmd7.Parameters.Add("UpdateTime", OracleType.DateTime).Value = reqTime;
                    insCmd7.Parameters.Add("Status", OracleType.VarChar).Value = "Y";
                    insCmd7.Parameters.Add("UUID", OracleType.VarChar).Value = regInfo.DeviceUUID;
                    insCmd7.Parameters.Add("PushType", OracleType.VarChar).Value = "N";

                    insCmd7.ExecuteNonQuery();
                }
                else
                {
                    insStr7 = "INSERT INTO device_token"
                            + " (CreateTime, UpdateTime, Status, UUID, PushType, DeviceToken) VALUES"
                            + " (:CreateTime, :UpdateTime, :Status, :UUID, :PushType, :DeviceToken)";
                    OracleCommand insCmd7 = new OracleCommand(insStr7, connOracle, myTrans);
                    insCmd7.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime;
                    insCmd7.Parameters.Add("UpdateTime", OracleType.DateTime).Value = reqTime;
                    insCmd7.Parameters.Add("Status", OracleType.VarChar).Value = "Y";
                    insCmd7.Parameters.Add("UUID", OracleType.VarChar).Value = regInfo.DeviceUUID;
                    insCmd7.Parameters.Add("PushType", OracleType.VarChar).Value = regInfo.PushType;
                    insCmd7.Parameters.Add("DeviceToken", OracleType.VarChar).Value = regInfo.DeviceToken;

                    insCmd7.ExecuteNonQuery();
                }

                //【08】更新 reg_apply 狀態
                //
                string updStr8 = "UPDATE reg_apply"
                  + " SET ApplyStatus='Y', StatusTime=:StatusTime, Pushed='N'"
                  + " WHERE pid=:ApplyID";
                OracleCommand updCmd8 = new OracleCommand(updStr8, connOracle, myTrans);
                updCmd8.Parameters.Add("StatusTime", OracleType.DateTime).Value = reqTime;
                updCmd8.Parameters.Add("ApplyID", OracleType.Int32).Value = regInfo.ApplyID;
                updCmd8.ExecuteNonQuery();


                //【10】新增朋友關係
                //
                if (regInfo.FriendPhone != regInfo.UserPhone
                  || regInfo.FriendCountry != regInfo.UserCountry)
                {
                    string countStr10 = "SELECT COUNT(*) FROM user_friend"
                      + " WHERE UserCountry=:UserCountry AND UserPhone=:UserPhone"
                      + " AND FriendCountry=:FriendCountry AND FriendPhone=:FriendPhone";
                    OracleCommand countCmd10 = new OracleCommand(countStr10, connOracle, myTrans);
                    countCmd10.Parameters.Add("UserCountry", OracleType.VarChar).Value = regInfo.UserCountry;
                    countCmd10.Parameters.Add("UserPhone", OracleType.VarChar).Value = regInfo.UserPhone;
                    countCmd10.Parameters.Add("FriendCountry", OracleType.VarChar).Value = regInfo.FriendCountry;
                    countCmd10.Parameters.Add("FriendPhone", OracleType.VarChar).Value = regInfo.FriendPhone;
                    UInt32 count = Convert.ToUInt32(countCmd10.ExecuteScalar());

                    if (count == 0)
                    {
                        string insStr10 = "INSERT INTO user_friend"
                          + " (UserCountry, UserPhone, FriendCountry, FriendPhone, CreateTime, Source)"
                          + " VALUES"
                          + " (:UserCountry, :UserPhone, :FriendCountry, :FriendPhone, :CreateTime, 'Register')";
                        OracleCommand insCmd10 = new OracleCommand(insStr10, connOracle, myTrans);
                        insCmd10.Parameters.Add("UserCountry", OracleType.VarChar).Value = regInfo.UserCountry;
                        insCmd10.Parameters.Add("UserPhone", OracleType.VarChar).Value = regInfo.UserPhone;
                        insCmd10.Parameters.Add("FriendCountry", OracleType.VarChar).Value = regInfo.FriendCountry;
                        insCmd10.Parameters.Add("FriendPhone", OracleType.VarChar).Value = regInfo.FriendPhone;
                        insCmd10.Parameters.Add("CreateTime", OracleType.DateTime).Value = reqTime;
                        insCmd10.ExecuteNonQuery();
                    }
                }


                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 清除所有開通前的聊天記錄
        /// </summary>
        /// <param name="SIP_Num">會員SIP號碼</param>
        private static void CleanAllOldChats(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            OracleTransaction myTrans = null;

            try
            {
                connOracle.Open();
                DateTime reqTime = DateTime.Now;

                myTrans = connOracle.BeginTransaction();

                //【01】單人聊天：放棄取得已讀標記(收訊人已讀)
                //
                string updStr1 = "UPDATE msg_one SET Status='L'"
                  + " WHERE Sender=:Sender AND Status='4'";
                OracleCommand updCmd1 = new OracleCommand(updStr1, connOracle, myTrans);
                updCmd1.Parameters.Add("Sender", OracleType.VarChar).Value = SIP_Num;
                updCmd1.ExecuteNonQuery();


                //【02】單人聊天：放棄取得已讀標記(收訊人未讀)
                //
                string updStr2 = "UPDATE msg_one SET ReadTagTime=:DateTime"
                  + " WHERE Sender=:Sender AND Status IN ('0','2')";
                OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle, myTrans);
                updCmd2.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                updCmd2.Parameters.Add("Sender", OracleType.VarChar).Value = SIP_Num;
                updCmd2.ExecuteNonQuery();


                //【03】群組聊天：放棄取得已讀標記
                //
                string updStr3 = "UPDATE group_msg SET Status='L'"
                  + " WHERE Sender=:Sender AND Status IN ('0','2')";
                OracleCommand updCmd3 = new OracleCommand(updStr3, connOracle, myTrans);
                updCmd3.Parameters.Add("Sender", OracleType.VarChar).Value = SIP_Num;
                updCmd3.ExecuteNonQuery();


                //【04】取消所有的訊息提醒Off
                //
                string updStr4 = "UPDATE msg_notify_off"
                  + " SET DisableTime=:DateTime"
                  + " WHERE DisableTime IS NULL AND SIP_Num=:SIP_Num";
                OracleCommand updCmd4 = new OracleCommand(updStr4, connOracle, myTrans);
                updCmd4.Parameters.Add("DateTime", OracleType.DateTime).Value = reqTime;
                updCmd4.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                updCmd4.ExecuteNonQuery();


                //【99】Commit Transaction
                //
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                if (myTrans != null)
                {
                    myTrans.Rollback();
                }
                throw ex;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 儲存中華電信SMS傳送結果
        /// </summary>
        /// <param name="ApplyID">註冊資料ID</param>
        /// <param name="HiNet_Code">傳送結果代碼</param>
        /// <param name="HiNet_Desc">傳送結果描述</param>
        public static void UpdateRegAuthHiNet(UInt32 ApplyID, short HiNet_Code, string HiNet_Desc)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                UpdateRegAuthHiNet(ApplyID, HiNet_Code, HiNet_Desc, connOracle);
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 儲存中華電信SMS傳送結果 (使用現有DB連線)
        /// </summary>
        /// <param name="ApplyID">註冊資料ID</param>
        /// <param name="HiNet_Code">傳送結果代碼</param>
        /// <param name="HiNet_Desc">傳送結果描述</param>
        /// <param name="connOracle">已連線的OracleConnection物件</param>
        private static void UpdateRegAuthHiNet(UInt32 ApplyID, short HiNet_Code, string HiNet_Desc, OracleConnection connOracle)
        {
            string updStr = "UPDATE reg_auth"
              + " SET HiNet_Code=:HiNet_Code, HiNet_Desc=:HiNet_Desc, SentTime=:SentTime"
              + " WHERE ApplyID=:ApplyID";
            OracleCommand updCmd = new OracleCommand(updStr, connOracle);
            updCmd.Parameters.Add("HiNet_Code", OracleType.Int16).Value = HiNet_Code;
            updCmd.Parameters.Add("HiNet_Desc", OracleType.VarChar).Value = HiNet_Desc;
            updCmd.Parameters.Add("SentTime", OracleType.DateTime).Value = DBNull.Value;
            updCmd.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;

            if (HiNet_Code == 0)
            {
                //Format="Successful:yyyyMMddHHmmss"
                string timeStr = HiNet_Desc.Substring(11, 14);
                DateTime? SentTime = ChrisKit.ParseDateTime(timeStr, "yyyyMMddHHmmss");
                if (SentTime.HasValue)
                    updCmd.Parameters["SentTime"].Value = SentTime.Value;
            }
            updCmd.ExecuteNonQuery();
        }


        /// <summary>
        /// 查詢中華電信HiAir之前尚未確認結果的記錄
        /// </summary>
        public static void CheckHiAirSent()
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                //【01】列出尚未確認傳送結果的MessageID
                //
                string selStr = "SELECT ApplyID, HiNet_ID FROM reg_auth"
                  + " WHERE LENGTH(HiNet_ID)=20"
                  + "   AND (HiNet_Code IN (1,2,4) OR HiNet_Code IS NULL)"
                  + "   AND CreateTime<=TIMESTAMPADD(HOUR,-1,:DateTime)"
                  + " ORDER BY CreateTime, ApplyID";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                Dictionary<UInt32, string> idDict = new Dictionary<UInt32, string>();
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    UInt32 ApplyID = Convert.ToUInt32(row["ApplyID"]);
                    string HiAirID = Convert.ToString(row["HiNet_ID"]);
                    idDict.Add(ApplyID, HiAirID);
                }

                //【02】儲存傳送結果到Database
                //
                foreach (UInt32 ApplyID in idDict.Keys)
                {
                    KeyValuePair<short, string>? result = SmsKit.QueryHiAirSMS(idDict[ApplyID]);

                    if (result.HasValue)
                    {
                        short HiAirCode = result.Value.Key;
                        string HiAirDesc = result.Value.Value;
                        UpdateRegAuthHiNet(ApplyID, HiAirCode, HiAirDesc, connOracle);
                    }
                    System.Threading.Thread.Sleep(100);
                }
            }
            catch
            {
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 儲存SMS備援服務傳送結果
        /// </summary>
        /// <param name="ApplyID">註冊資料ID</param>
        /// <param name="AltSMSID">SMS備援服務訊息ID</param>
        public static void UpdateAltSMSResp(UInt32 ApplyID, string AltSMSID)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string updStr = "UPDATE reg_auth"
                  + " SET AltSMSID=:AltSMSID, SentTime=:DateTime"
                  + " WHERE ApplyID=:ApplyID";
                OracleCommand updCmd = new OracleCommand(updStr, connOracle);
                updCmd.Parameters.Add("AltSMSID", OracleType.VarChar).Value = AltSMSID;
                updCmd.Parameters.Add("DateTime", OracleType.DateTime).Value = DateTime.Now;
                updCmd.Parameters.Add("ApplyID", OracleType.Int32).Value = ApplyID;
                updCmd.ExecuteNonQuery();
            }
            finally
            {
                connOracle.Close();
            }
        }


    }


    /// <summary>
    /// User註冊資料
    /// </summary>
    public class RegApplyInfo
    {
        private int _ApplyID;//註冊資料ID
        private string _DeviceType; //裝置類別
        private string _UserCountry;//手機門號國碼
        private string _UserPhone;  //手機門號
        private string _FullName;   //Full Name
        private string _UserID;   //User員工編號
        private string _UserType;   //User身分類別
        private string _FriendCountry;//CCPSIP朋友手機
        private string _FriendPhone;  //CCPSIP朋友手機
        private string _Remark;       //備註
        private DateTime _StatusTime; //註冊更新時間
        private string _DeviceUUID; //Device UUID
        private string _DeviceToken;//Device Push Token
        private string _PushType;   //推播類型
        private string _OSVer;      //OS類型與版本

        public int ApplyID { get { return this._ApplyID; } }
        public string DeviceType { get { return this._DeviceType; } }
        public string UserCountry { get { return this._UserCountry; } }
        public string UserPhone { get { return this._UserPhone; } }
        public string FullName { get { return this._FullName; } }
        public string UserID { get { return this._UserID; } }
        public string UserType { get { return this._UserType; } }
        public string FriendCountry { get { return this._FriendCountry; } }
        public string FriendPhone { get { return this._FriendPhone; } }
        public string Remark { get { return this._Remark; } }
        public DateTime StatusTime { get { return this._StatusTime; } }
        public string DeviceUUID { get { return this._DeviceUUID; } }
        public string DeviceToken { get { return this._DeviceToken; } }
        public string PushType { get { return this._PushType; } }
        public string OSVer { get { return this._OSVer; } }

        public RegApplyInfo(DataRow row)
        {
            _ApplyID = Convert.ToInt32(row["pid"]);
            _DeviceType = Convert.ToString(row["DeviceType"]);
            _UserCountry = Convert.ToString(row["UserCountry"]);
            _UserPhone = Convert.ToString(row["UserPhone"]);
            _FullName = Convert.ToString(row["FullName"]);
            _UserType = Convert.ToString(row["UserType"]);
            _FriendCountry = Convert.ToString(row["FriendCountry"]);
            _FriendPhone = Convert.ToString(row["FriendPhone"]);
            _Remark = Convert.ToString(row["Remark"]);
            _StatusTime = Convert.ToDateTime(row["StatusTime"]);
            _DeviceUUID = Convert.ToString(row["DeviceUUID"]);
            _OSVer = Convert.ToString(row["OSVer"]);

            if (row["UserID"] != DBNull.Value)
            {
                _UserID = Convert.ToString(row["UserID"]);
            }

            if (row.Table.Columns.Contains("DeviceToken"))
            {
                if (row["DeviceToken"] != DBNull.Value)
                {
                    _DeviceToken = Convert.ToString(row["DeviceToken"]);
                    _PushType = Convert.ToString(row["PushType"]);
                }
            }
        }
    }
}